﻿using System;
using System.Configuration;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;



/// <summary>
/// class  SqlNewsProvider 
/// Provides access to SqlServer data source
/// </summary>
public class SqlNewsProvider : NewsProvider
{
    /// <summary>
    /// Reads xml and xsd file names from the web.config file
    /// </summary>
    private string connectionString()
    {
        SmallBusinessDataProvidersSection sec = (ConfigurationManager.GetSection("SmallBusinessDataProviders")) as SmallBusinessDataProvidersSection;
        string connectionStringName = sec.NewsProviders[sec.NewsProviderName].Parameters["connectionStringName"];
        return WebConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
    }
    //另一种数据连接
    public static SqlConnection conn()
    {
        string sqlstr = ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString;
        SqlConnection con = new SqlConnection(sqlstr);
        return con;
    }
    SqlConnection con = conn();

    // DateTime.MinValue returned if no date is specified
    public override List<NewsItem> GetAllNews()
    {

        List<NewsItem> list = new List<NewsItem>();

        // create a connection, invoke a stored procedure and add results to a list
        using (SqlConnection con = new SqlConnection(connectionString()))
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("GetNews", con);
            SqlDataReader r = cmd.ExecuteReader();
            NewsItem curr;
            while (r.Read())
            {
                if (r["id"] is DBNull || r["visible"] is DBNull || r["title"] is DBNull)
                    throw new InvalidOperationException(Messages.NewsItemRequiredAttributesMissing);

                curr = new NewsItem((string)r["id"], (Boolean)r["visible"], (string)r["title"]);
                curr.Date = (r["date"] is DBNull) ? DateTime.MinValue : (DateTime)r["date"];
                curr.Content = (r["content"] is DBNull) ? String.Empty : (string)r["content"];
                curr.ImageUrl = (r["imageUrl"] is DBNull) ? String.Empty : (string)r["imageUrl"];
                curr.ImageAltText = (r["imageAltText"] is DBNull) ? String.Empty : (string)r["imageAltText"];
                list.Add(curr);
            }
            con.Close();
        }

        return list;
    }

    public override NewsItem GetNewsItem(string newsItemId)
    {

        if (String.IsNullOrEmpty(newsItemId)) return null;

        // connect to the database
        NewsItem curr;
        using (SqlConnection con = new SqlConnection(connectionString()))
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("GetNewsItem", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@id", SqlDbType.NVarChar);
            cmd.Parameters["@id"].Value = newsItemId;

            SqlDataReader r = cmd.ExecuteReader();

            r.Read();

            if (r["id"] is DBNull || r["visible"] is DBNull || r["title"] is DBNull)
                throw new InvalidOperationException(Messages.NewsItemRequiredAttributesMissing);

            curr = new NewsItem((string)r["id"], (Boolean)r["visible"], (string)r["title"]);
            curr.Date = (r["date"] is DBNull) ? DateTime.MinValue : (DateTime)r["date"];
            curr.Content = (r["content"] is DBNull) ? String.Empty : (string)r["content"];
            curr.ImageUrl = (r["imageUrl"] is DBNull) ? String.Empty : (string)r["imageUrl"];
            curr.ImageAltText = (r["imageAltText"] is DBNull) ? String.Empty : (string)r["imageAltText"];
        }

        return curr;
    }
    /// <summary>
    /// 新闻发布数据插入
    /// </summary>
    /// <param name="txttitle"></param>
    /// <param name="txtcontent"></param>
    /// <param name="txtimageurl"></param>
    /// <param name="imagetext"></param>
    public void InsertNews(string id,bool visible,string txttitle, string txtcontent, string txtimageurl, string imagetext)
    {
        using (con)
        {
            con.Open();
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = con;
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.CommandText = "InsertNews";

            //往存储过程中添加参数
            sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@id", System.Data.SqlDbType.NVarChar, 32));
            sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@visible", System.Data.SqlDbType.Bit));
            sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@title", System.Data.SqlDbType.NVarChar, 256));
            sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@content", System.Data.SqlDbType.NVarChar, 4000));
            sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@imageUrl", System.Data.SqlDbType.NVarChar, 256));
            sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@imageAltText", System.Data.SqlDbType.NVarChar, 256));

            //给存储过程的参数付值
            sqlCommand.Parameters["@id"].Value = id.ToString();
            sqlCommand.Parameters["@visible"].Value = visible;
            sqlCommand.Parameters["@title"].Value = txttitle.ToString();
            sqlCommand.Parameters["@content"].Value = txtcontent.ToString();
            sqlCommand.Parameters["@imageUrl"].Value = txtimageurl.ToString();
            sqlCommand.Parameters["@imageAltText"].Value = imagetext.ToString();
            sqlCommand.ExecuteNonQuery();
            con.Close();
        }

    }
}// end class

